package il.ac.tau.dbcourse.freedb;

import gnu.trove.map.TLongIntMap;
import gnu.trove.map.hash.TLongIntHashMap;
import il.ac.tau.dbcourse.db.PersistenceManager;
import il.ac.tau.dbcourse.freedb.FreeDbParser.Track;

import java.io.PrintStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Date;
import java.util.List;

// TODO: Flag associations from updated albums/tracks

public class FreeDbDataLoader {			
	private static final int BATCH_SIZE = 10000;
	private PrintStream printStream;
	
	Connection connection;
	PreparedStatement insertAlbumStmt;
	PreparedStatement insertArtistStmt;
	PreparedStatement insertTrackStmt;
	PreparedStatement insertGenreStmt;	
	
	PreparedStatement selectArtistNextIdStmt;
	PreparedStatement selectTrackNextIdStmt;
	PreparedStatement selectGenreNextIdStmt;
	PreparedStatement selectAlbumNextIdStmt;
		
	TLongIntMap genreMap = new TLongIntHashMap(10000);
	//Map<String, Integer> genreMap = new HashMap<String, Integer>(); 
	TLongIntMap artistMap = new TLongIntHashMap(100000);	
	//Map<Long, Integer> artistMap = new HashMap<Long, Integer>(3000000);
		
	private Integer artistId;
	private Integer albumId;
	private Integer trackId;
	private Integer genreId;
	int batchCounter = 0;			
	public long flushTime;
	
	public FreeDbDataLoader(Connection connection, PrintStream printSt) throws SQLException {		
		this.connection = connection;											
		this.printStream = printSt;
		
		createInsertStmts();
		
		selectArtistNextIdStmt= connection.prepareStatement("select \"ARTIST_SEQ\".nextval FROM dual");
		selectAlbumNextIdStmt= connection.prepareStatement("select \"ALBUM_SEQ\".nextval FROM dual");
		selectGenreNextIdStmt= connection.prepareStatement("select \"GENRE_SEQ\".nextval FROM dual");
		selectTrackNextIdStmt= connection.prepareStatement("select \"TRACK_SEQ\".nextval FROM dual");
		
		toggleAutoIDs(false);
	}
	
	protected long RSHash(String str)
	{
	    int b = 378551;
	    int a = 63689;
	    long hash = 0L;
	    for(int i = 0; i < str.length(); i++)
	    {
	        hash = hash * (long)a + (long)str.charAt(i);
	        a *= b;
	    }
	
	    return hash;
	}
	 
	private void createInsertStmts() throws SQLException {				
		closeInsertStmts();					
		
		insertArtistStmt = connection.prepareStatement("INSERT INTO ARTIST VALUES (?, ?)");		
		insertGenreStmt = connection.prepareStatement("INSERT INTO GENRE VALUES (?, ?)");
		insertAlbumStmt = connection.prepareStatement("INSERT INTO ALBUM VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
		insertTrackStmt = connection.prepareStatement("INSERT INTO TRACK VALUES (?, ?, ?, ?, ?, ?)");
	}

	private void updateSeq(String seqStr, Integer appSeq) throws SQLException {
		if (appSeq == null)
			return;
		Statement stmt = connection.createStatement();
		ResultSet rs;
		try {
			rs = stmt.executeQuery("SELECT \"" + seqStr
					+ "\".CURRVAL FROM DUAL");
		} catch (SQLException e) {
			// 8002 - CURRVAL wasn't in use - trying to update index that wasn't
			// in use. Can ignore that.
			if (e.getErrorCode() == 8002)
				return;
			// otherwise, we had a serious problem
			throw e;
		}
		rs.next();
		Long currSeq=rs.getLong(1);
		rs.close();
		Long count=appSeq-currSeq+1;
		if (count<0) return;
		
		stmt.execute("ALTER SEQUENCE \""+seqStr+"\" INCREMENT BY "+count);
		stmt.execute("SELECT \""+seqStr+"\".NEXTVAL FROM DUAL");
		stmt.execute("ALTER SEQUENCE \""+seqStr+"\" INCREMENT BY 1");
		stmt.close();
	}
	
	private void resetSeq(String seqStr) throws SQLException{
		Statement stmt = connection.createStatement();
		stmt.execute("DROP SEQUENCE\""+seqStr+"\"");
		stmt.execute("CREATE SEQUENCE\""+seqStr+"\"");
		stmt.close();
	}		 		
		
	private int getNextArtistId() throws SQLException{
		if (artistId!=null) return ++artistId;
		ResultSet rs = selectArtistNextIdStmt.executeQuery();
		rs.next();
		artistId=rs.getInt(1);
		rs.close();
		return artistId;
	}
	
	private void updateArtistSeq() throws SQLException{
		updateSeq("ARTIST_SEQ",artistId);
	}
	
	private int getNextTrackId() throws SQLException{
		if (trackId!=null) return ++trackId;
		ResultSet rs = selectTrackNextIdStmt.executeQuery();
		rs.next();
		trackId=rs.getInt(1);
		rs.close();
		return trackId;
	}
	
	private void updateTrackSeq() throws SQLException{
		updateSeq("TRACK_SEQ",trackId);
	}

		
	private int getNextGenreId() throws SQLException{
		if (genreId!=null) return ++genreId;
		ResultSet rs = selectGenreNextIdStmt.executeQuery();
		rs.next();
		genreId=rs.getInt(1);
		rs.close();
		return genreId;
	}
	private void updateGenreSeq() throws SQLException{
		updateSeq("GENRE_SEQ",genreId);
	}

	private void updateAlbumSeq() throws SQLException {
		updateSeq("ALBUM_SEQ", albumId);
	}

	private int getNextAlbumId() throws SQLException{
		if (albumId!=null) return ++albumId;
		ResultSet rs = selectAlbumNextIdStmt.executeQuery();
		rs.next();
		albumId=rs.getInt(1);
		rs.close();
		return albumId;
	}

	private void toggleCTXindices(boolean flag) throws SQLException {
		if (flag)
			PersistenceManager.rebuildCTXindices(connection);
		else 
			PersistenceManager.disableCTXindices(connection);
	}

	private void toggleAutoIDs(boolean flag) throws SQLException {
		PersistenceManager.toggleAutoIDs(connection, flag);
	}		 	
	
	public void truncate() throws SQLException {
		if (printStream!=null) printStream.println("Truncating existing tables...");
		// Drop full text indexes
		toggleCTXindices(false);
		PersistenceManager.truncate(connection);
	}
	
	public void rebuildIndices() throws SQLException {
		if (printStream!=null) printStream.println("Rebulding indices...");
		toggleCTXindices(true);
	}
	
	Integer getGenreId(String genre) throws Exception {
		if (genre.length() == 0) return null;				
		
		long genreHash = RSHash(genre);				
		
		if (!genreMap.containsKey(genreHash)) {							
			genreId=getNextGenreId();
			insertGenreStmt.setInt(1, genreId);
			insertGenreStmt.setString(2, genre);			
			insertGenreStmt.addBatch();
			genreMap.put(genreHash, genreId);
			return genreId;
		}
		else {			
			return genreMap.get(genreHash);
		}
						
	}
	
	Integer getArtistId(String artist) throws Exception {
		if (artist == null)
			return null;
		
		long artistHash = RSHash(artist);				
					
		if (!artistMap.containsKey(artistHash)) {					
			artistId=getNextArtistId();
			insertArtistStmt.setInt(1, artistId);
			insertArtistStmt.setString(2, artist);			
			insertArtistStmt.addBatch();
						
			batchCounter++;
			
			artistMap.put(artistHash, artistId);
			return artistId;
		}
		else {
			return artistMap.get(artistHash);
		}						
	}
			
	// Yonatan's change - allow to deactive indexing (safely)
	public void deferIndices() throws SQLException {
		toggleCTXindices(false);
	}
	
	public void close() throws SQLException {
		flush();			
		
		closeInsertStmts();		
		
		selectAlbumNextIdStmt.close();
		selectArtistNextIdStmt.close();
		selectGenreNextIdStmt.close();
		selectTrackNextIdStmt.close();
		
		updateAlbumSeq();
		updateGenreSeq();
		updateTrackSeq();
		updateArtistSeq();
		
		toggleAutoIDs(true);
	}
	
	public void flush() throws SQLException {
		long st = System.nanoTime();
		
		if (insertArtistStmt != null) insertArtistStmt.executeBatch();						
		if (insertGenreStmt != null) insertGenreStmt.executeBatch();
		if (insertAlbumStmt != null) insertAlbumStmt.executeBatch();
		if (insertTrackStmt != null) insertTrackStmt.executeBatch();
		
		flushTime += (System.nanoTime() - st);
	}
	
	public void closeInsertStmts() throws SQLException {						
		if (insertArtistStmt != null) insertArtistStmt.close();						
		if (insertGenreStmt != null) insertGenreStmt.close();
		if (insertAlbumStmt != null) insertAlbumStmt.close();
		if (insertTrackStmt != null) insertTrackStmt.close();			
	}		
		
	public void insertAlbum(String freeDbId, String artist, String title, int length, int year, String genre, int revision, List<FreeDbParser.Track> tracks) throws Exception {
		Integer genreId = getGenreId(genre); 
		Integer artistId = getArtistId(artist);				
		
		// Add the album
		Integer albumId=getNextAlbumId();
		insertAlbumStmt.setInt(1, albumId);
		insertAlbumStmt.setString(2, freeDbId);						
		insertAlbumStmt.setObject(3, genreId, Types.BIGINT);		
		insertAlbumStmt.setInt(4, length);
		insertAlbumStmt.setInt(5, year);						
		insertAlbumStmt.setObject(6, artistId, Types.BIGINT);		
		insertAlbumStmt.setString(7, title);
		insertAlbumStmt.setInt(8, revision);
		insertAlbumStmt.addBatch();			
		batchCounter++;
				
		// Add the album's tracks
		int trackNumber = 0;
		int trackId;
		for (Track track: tracks) {
			trackNumber++;
			trackId=getNextTrackId();
			Integer trackArtistId = null;
			if (track.getArtist() != null) { 
				trackArtistId = getArtistId(track.getArtist());
			}
			
			insertTrackStmt.setInt(1, trackId);
			insertTrackStmt.setString(2, track.getTitle());
			insertTrackStmt.setInt(3, track.getLength());
			insertTrackStmt.setInt(4, albumId);
			insertTrackStmt.setInt(5, trackNumber);				
			insertTrackStmt.setObject(6, trackArtistId, Types.BIGINT);
			insertTrackStmt.addBatch();
			batchCounter++;			
		}		
		
		if (batchCounter > BATCH_SIZE) {
			flush();
			batchCounter -= BATCH_SIZE;
		}		
	}	
}

